package zhuzx.solution.ir;

import java.util.HashSet;
import java.util.List;
import java.util.Map;

import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.ListDataUtil;
import org.zhuzx.util.MicroUtil;

public class IrClockInErrFinder {
	
	private static final String SQL_DISSIMILAR_PHONE =
			"select t1.user_id, t1.name, t1.clock_in_date,\r\n" + 
			"max(t2.create_date) as time, t1.phone_number as aph, t2.phone_number as cph,\r\n" + 
			"t1.qywx_org_name from OA.IR_ADDRESS_BOOK t1\r\n" + 
			"inner join OA.IR_CLOCK_IN_INFO t2 on t1.user_id = t2.user_id\r\n" + 
			"where t1.clock_in_date < '%s'\r\n" + 
			"and t2.create_date >= '%s'\r\n" + 
			"and t1.phone_number != t2.phone_number and t1.name = %s\r\n" + 
			"group by t1.user_id, t1.name, t1.clock_in_date, t1.phone_number, t2.phone_number,\r\n" + 
			"t1.qywx_org_name order by time desc";

	private static final String SQL_CONFLICTING_PHONE =
			"select t1.qywx_org_name as org, t1.name, t1.PHONE_NUMBER as aph, t1.user_id,\r\n" + 
			"t1.clock_in_date\r\n" + 
			"  from OA.IR_ADDRESS_BOOK t1,\r\n" + 
			"       (select count(1) as count, PHONE_NUMBER\r\n" + 
			"          from OA.IR_ADDRESS_BOOK\r\n" + 
			"         GROUP BY PHONE_NUMBER) t\r\n" + 
			" where t1.PHONE_NUMBER = t.PHONE_NUMBER\r\n" + 
			"   and t.count > 1";
	
	private static final String GUIDE_MODIFY_OA_PHONE =
			"每个员工在OA中都有登记个人信息的，OA账号是凭员工号自助注册，"
			+ "所以不存在哪个员工没有OA账号的问题，请务必让他及时处理。\n"
			+ "健康申报这边登记的手机号，是从OA自动同步过来的，会有一定延时，"
			+ "你让员工在OA改了手机号之后，等待一小时左右，系统会完成同步，"
			+ "然后让员工再打一次卡，就能得到正确的健康申报打卡数据。";
			
	private static final String TEMPLATE_DISSIMILAR_PHONE =
			"查询到${NAME}在健康申报系统中登记的手机号为：${APH}，与其在打卡时填写的手机号${CPH}不一致，"
					+ "请通知该员工登录OA修改手机号，修改后等待1小时，再重新打健康申报卡。\n"
					+ "提示：\n" + GUIDE_MODIFY_OA_PHONE;
	
	private static final String TEMPLATE_WAIT_PULL =
			"您OA中登记的手机号%s与健康申报系统中登记的手机号%s不一致，请等待1小时后重新打一次健康申报卡"
			+ "（等系统自动更新手机号）。"
			+ "若重新打卡后仍旧显示未打卡，请再与本人联系。";
	
	private static final String TEMPLATE_CONFLICTING_PHONE =
			"检测到手机号冲突，${NAME}在健康申报系统中登记的手机号${APH}已被其他员工使用，"
			+ "若确定该手机号是${NAME}本人使用，请尽快与员工【${NAME_OTH}】联系让其修改。\n"
			+ "否则请上OA修改${NAME}的手机号，手机号冲突会导致员工明明已成功提交了健康申报，却仍显示未打卡。"
			+ "\nOA中登记为相同手机号${APH}的员工如下：\n"
			+ "${ORG}，员工姓名：${NAME}。\n"
			+ "${ORG_OTH}，员工姓名：${NAME_OTH}。\n"
			+ "提示：\n" + GUIDE_MODIFY_OA_PHONE;
	
	private static final String TEMPLATE_CONFLICTING_REDUNDANCY =
			"检测到健康申报系统中存在多个姓名为${NAME}，手机号${APH}的员工档案，导致数据在这多个档案之间错乱，"
			+ "请尽快确认该员工最新的组织归属并发送给我，我必须将其它的多余员工档案删除，否则打卡状态异常的问题会一直存在。"
			+ "\n同名同手机号的员工信息如下，为避免误删，请确认是何种具体情况，比如部门名称变更、转岗、子公司间调动，"
			+ "以及是否有可能是真的同名同姓，只是误窜了手机号等等：\n${data}";
	
	
	private static String specifiedDate = null;
	
	public static void main(String[] args) {
		executeCheck();
	}
	
	static void executeCheck() {
		String name = "陈泽辉";
		String phone = "";
		String org = "";
		if (GeneralUtil.isBlank(name)) {
			throw new RuntimeException("name不能为空");
		}
		String errMsg = IrUserInserter.insert(name, phone);
		if (errMsg != null && errMsg.contains(IrUserInserter.NORMAL_KEY)) {
			errMsg = isConflictingPhone(name, phone, org);
			if (errMsg == null) {
				errMsg = isDissimilarPhone(name, phone, org);
			}
		}
		IrDataHelper.showResult(errMsg);
	}

	private static List<Map<String,String>> queryConflictingPhone(
			String name, String phone, String org) {
		String exp = "";
		if (GeneralUtil.isNotBlank(name)) {
			exp += "\nand t1.name = '" + name + "'";
		}
		if (GeneralUtil.isNotBlank(phone)) {
			exp += "\nand (t1.phone_number = '" + phone + "'" +
					" or t.phone_number = '" + phone + "')";
		}
		exp += IrDataHelper.createOrgExp("t1.qywx_org_name", org);
		String sql = SQL_CONFLICTING_PHONE + exp;
		List<Map<String,String>> list = IrDataHelper.query("isConflictingPhone", sql, name);
		return list;
	}
	
	static String isConflictingPhone(String name, String phone, String org) {
		List<Map<String, String>> list = queryConflictingPhone(name, phone, org);
		if (list.size() == 0) {
			return null;
		} else if (list.size() == 1) {//加了姓名条件的话很可能只查出一条记录，要用手机号重新查。
			if (GeneralUtil.isNotBlank(name)) {
				phone = list.get(0).get("APH");
				list = queryConflictingPhone(null, phone, null);
			} else {
				throw new RuntimeException("根据手机号查只有一条");
			}
		}
		
		//同名同手机的情况，很可能是同一个人留了冗余记录，待人工确认后在后台做删除操作。
		List<String> nameList = ListDataUtil.getValues(list, "NAME");
		if (new HashSet<String>(nameList).size() == 1) {
			Map<String,String> single = list.get(0);
			single.put("data", GeneralUtil.getCombinedString(list, "\n"));
			return MicroUtil.fillParameters(TEMPLATE_CONFLICTING_REDUNDANCY, single);
		}
		
		//确实是手机号冲突的情况
		int index = 0;
		Map<String,String> info = null;
		while (index < list.size()) {
			info = list.get(index);
			if (info.get("NAME").equals(name)) {
				list.remove(index);
				break;
			} else {
				index++;
			}
		}
		Map<String,String> oth = list.get(0);
		info.put("NAME_OTH", oth.get("NAME"));
		info.put("ORG_OTH", oth.get("ORG"));
		String res = MicroUtil.fillParameters(TEMPLATE_CONFLICTING_PHONE, info);
		return res;
	}
	
	/**
	 * 判断是否有手机号不一致的问题，若手机号没问题，再检查打卡状态和打卡记录的一致性，确定该员工是否真有数据问题。
	 * 这个判断要放在手机号冲突检测的后面做，因为会被其干扰。
	 * 本方法作为下游检测逻辑，若返回null则有问题，需要人工干预。
	 */
	static String isDissimilarPhone(String name, String phone, String org) {
		String exp = "'" + name + "'";
		if (GeneralUtil.isNotBlank(phone)) {
			exp += " and (t1.phone_number = '" + phone + "'" +
					" or t2.phone_number = '" + phone + "')";
		}
		if (GeneralUtil.isNotBlank(org)) {
			String convertedOrg = IrDataHelper.convertBrackets(org);
			exp += " and (t1.qywx_org_name like '%" + org + "%'" +
					" or t1.qywx_org_name like '%" + convertedOrg + "%'" +
					" or t2.company_name like '%" + org + "%'" +
					" or t2.company_name like '%" + convertedOrg + "%')";
		}
		String dateParam = GeneralUtil.getNotBlankString(
				specifiedDate, GeneralUtil.getDate());
		String timeParam = dateParam + " 00:00:00";
		String sql = String.format(SQL_DISSIMILAR_PHONE,
				dateParam, timeParam, exp);
		List<Map<String,String>> list = IrDataHelper.query("isDissimilarPhone", sql, name);
		if (list.size() != 1) {
			//正常情况下只有0或1条记录，但可能有重名的多个异常员工，这时需要增加筛选条件或者人工排查。
			if (list.size() == 0) {//如果为0，那可能确实是正常数据，进一步排查确认。
				sql = sql.replace("and t1.phone_number != t2.phone_number ", "");
				list = IrDataHelper.query("isAlreadyCorrectState", sql, name);
				if (list.size() == 0) {
					String res = name + "的健康打卡数据正常，请确认是否已为您处理过了。"
							+ "（我可能会不定时统一处理此类问题，而未能及时通知到个人，请见谅）\n"
							+ "或者有可能是这段时间内，你们自己解决了手机号问题从而修正了状态"
							+ "（包括手机号被他人占用的情况）。";
					return res;
				} else {
					throw new RuntimeException("运行出错：发现号码一致，但打卡状态异常。\n"
							+ "在手机号冲突检测执行后不应出现这个问题！");
				}
			}
			return null;//找到多个，可能是重名，需要人工干预。
		}
		String waitMsg = checkOaPerson(name);
		if (waitMsg != null) {
			return waitMsg;
		}
		Map<String,String> info = list.get(0);
		return MicroUtil.fillParameters(TEMPLATE_DISSIMILAR_PHONE, info);
	}
	
	/**
	 * 检查是否存在OA的person表与address_book表中手机号不一致的情况
	 */
	private static String checkOaPerson(String name) {
		String sql = "select person.UNNO, person.name, person.MOBILE as PPH,"
				+ "person.update_date, book.PHONE_NUMBER as APH\r\n" + 
				"from OA.OA_PERSON person left join OA.IR_ADDRESS_BOOK book\r\n" + 
				"on person.UNNO = BOOK.USER_ID\r\n" + 
				"where person.delete_status = 0 and person.locked_status = 0\r\n"
				+ "and person.MOBILE != BOOK.PHONE_NUMBER\r\n"
				+ "and (person.name = '" + name + "' or book.name = '" + name + "')";
		List<Map<String,String>> list = IrDataHelper.query("checkOaPerson", sql, name);
		if (GeneralUtil.isEmpty(list)) {
			return null;
		} else {
			if (list.size() > 1) {
				if (new HashSet<String>(ListDataUtil.getValues(list, "PPH")).size() == 1) {
					throw new RuntimeException("根据【" + name + "】查到两条person记录，"
							+ "都与book表中手机号不一致，并且两条记录本身的手机号也不一致，"
							+ "需要检查person表数据。");
				}
			}
			Map<String,String> info = list.get(0);
			return String.format(TEMPLATE_WAIT_PULL, info.get("PPH"), info.get("APH"));
		}
	}
	
}